The present disclosure relates to systems and methods for efficiently paging through large data sets.
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. Retrieval of information from a database is typically done using queries. A query often specifies conditions that apply to one or more columns of the database, and may specify relatively complex logical operations on multiple columns. The database is searched for records that satisfy the query, and those records are returned as the query result.
It is commonly recommended to use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows sent to a client application. Using OFFSET and FETCH as a paging solution requires running the query one time for each “page” of data returned to the client application. For example, to return the results of a query in 10-row increments, the query must be executed one time to return rows 1 to 10 and then the query must run again to return rows 11 to 20 and so on. Each query is independent and not related to each other in any way. This means that, unlike using a cursor in which the query is executed once and the state is maintained on the server, the client application is responsible for tracking the state. Accordingly, to achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met: (1) the underlying data that is used by the query must not change—that is, either the rows touched by the query are not updated or all requests for pages from the query are executed in a single transaction using either snapshot or serializable transaction isolation; and (2) the ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.